insert overwrite table jms_dm.dm_transfer_bill_count_sum_dt partition (dt)
select
  t.scan_time_ff as scan_time
 ,t.site_code --网点
 ,t.site_name --网点名称
 ,t.regional_id --大区id
 ,t.regional_desc --大区
 ,t.agent_code --代理区
 ,t.agent_name --代理区
 ,sum(case when t.site_weight=1 then 1 else 0 end) as in_bill_num --  进港件量
 ,sum(case when t.site_weight=1 then t.res_weight else 0 end ) as in_bill_weight --进港重量
 ,sum(case when t.site_weight=2 then 1 else 0 end ) as zz_bill_num --中转件量
 ,sum(case when t.site_weight=2 then t.res_weight else 0 end  ) as zz_bill_weight --中转重量
 ,sum(case when t.site_weight=3 then 1 else 0 end  ) as out_bill_num --出港件量
 ,sum(case when t.site_weight=3 then t.res_weight else 0 end ) as out_bill_weight--出港重量
  ,t.waybill_source_code
 ,t.waybill_source_name
 ,t.dt
from jms_dm.dm_transfer_bill_count_detail_dt t
where  t.dt = '{{ execution_date | cst_ds }}'
group by
  t.dt
 ,t.scan_time_ff
 ,t.site_code
 ,t.site_name
 ,t.regional_id --大区id
 ,t.regional_desc --大区
 ,t.agent_code --代理区
 ,t.agent_name --代理区
 ,t.waybill_source_code
 ,t.waybill_source_name

 distribute by pmod(hash(rand()), 3);
